﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
using System.Data.Common;
using System.Data;
using System.Collections;
using System.IO;
using System.Xml.Serialization;
using System.IO;
using AMStudio.Log;

namespace AMStudio.Common
{
    public class WorkorderDAL
    {
        //private DataSet myDataset;

        public WorkorderDAL()
        {
            //FillDataTable();
            //Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("UIDB");
            //myDataset = new DataSet();
            //Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("MachineConfig");
            //string sql = "select * from [products$]";
            //DbCommand cmd = db.GetSqlStringCommand(sql);

            //string productsTable = "[products$]";

            //// Retrieve the initial data.
            //db.LoadDataSet(cmd, myDataset, productsTable);

            //// Get the table that will be modified.
            //DataTable dTable = myDataset.Tables[productsTable];



        }


        public bool Add(WorkOrderItem item)
        {
            Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("UIDB");
            StringBuilder sb = new StringBuilder();
            sb.Append(@"INSERT INTO [workorder] ([Name], [Status], [Privilege], [Quantity], [Finished], [Remaining], [StartTime], [Endtime], [Pass], [Fail], [Comments],[Product], [ProductNO]) VALUES " +
                @"(@Name, @Status, @Privilege, @Quantity, @Finished, @Remaining, @StartTime, @Endtime, @Pass, @Fail, @Comments,@Product, @ProductNO)");
            DbCommand cmd = db.GetSqlStringCommand(sb.ToString());
            db.AddInParameter(cmd, "@Name", DbType.String, item.Name);
            db.AddInParameter(cmd, "@Status", DbType.Int32, item.Status);
            db.AddInParameter(cmd, "@Privilege", DbType.Int32, item.Privilege);
            db.AddInParameter(cmd, "@Quantity", DbType.Int32, item.Quantity);
            db.AddInParameter(cmd, "@Finished", DbType.Int32, item.Finished);
            db.AddInParameter(cmd, "@Remaining", DbType.Int32, item.Remaining);
            db.AddInParameter(cmd, "@StartTime", DbType.String, item.StartTime);
            db.AddInParameter(cmd, "@Endtime", DbType.String, item.Endtime);
            db.AddInParameter(cmd, "@Pass", DbType.Int32, item.Pass);
            db.AddInParameter(cmd, "@Fail", DbType.Int32, item.Fail);
            db.AddInParameter(cmd, "@Comments", DbType.String, item.Comments);
            db.AddInParameter(cmd, "@Product", DbType.String, item.Product);
            db.AddInParameter(cmd, "@ProductNO", DbType.String, item.ProductNO);
            return db.ExecuteNonQuery(cmd) > 0 ? true : false;
        }

        public bool Update(WorkOrderItem item)
        {
            Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("UIDB");
            StringBuilder sb = new StringBuilder();
            sb.Append("update workorder set ");
            sb.Append("[Status]=@Status, ");
            sb.Append("[Privilege]=@Privilege, ");
            sb.Append("[Quantity]=@Quantity, ");
            sb.Append("[Finished]=@Finished, ");
            sb.Append("[Remaining]=@Remaining, ");
            sb.Append("[StartTime]=@StartTime, ");
            sb.Append("[Endtime]=@Endtime, ");
            sb.Append("[Pass]=@Pass, ");
            sb.Append("[Fail]=@Fail, ");
            sb.Append("[Comments]=@Comments, ");
            sb.Append("[Product]=@Product, ");
            sb.Append("[ProductNO]=@ProductNO ");
            
 
			sb.Append(" where [Name]=@Name");
			 DbCommand cmd = db.GetSqlStringCommand(sb.ToString());
               
            db.AddInParameter(cmd, "@Name", DbType.String, item.Name);
            db.AddInParameter(cmd, "@Status", DbType.Int32, item.Status);
            db.AddInParameter(cmd, "@Privilege", DbType.Int32, item.Privilege);
            db.AddInParameter(cmd, "@Quantity", DbType.Int32, item.Quantity);
            db.AddInParameter(cmd, "@Finished", DbType.Int32, item.Finished);
            db.AddInParameter(cmd, "@Remaining", DbType.Int32, item.Remaining);
            db.AddInParameter(cmd, "@StartTime", DbType.String, item.StartTime);
            db.AddInParameter(cmd, "@Endtime", DbType.String, item.Endtime);
            db.AddInParameter(cmd, "@Pass", DbType.Int32, item.Pass);
            db.AddInParameter(cmd, "@Fail", DbType.Int32, item.Fail);
            db.AddInParameter(cmd, "@Comments", DbType.String, item.Comments);
            db.AddInParameter(cmd, "@Product", DbType.String, item.Product);
            db.AddInParameter(cmd, "@ProductNO", DbType.String, item.ProductNO);
            int r = db.ExecuteNonQuery(cmd);
            return r > 0 ? true : false; ;
        }

        public bool Delete(string name)
        {
            Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("UIDB");
            StringBuilder sb = new StringBuilder();
            sb.Append("delete from workorder ");
            sb.Append(" where Name=@Name");
            DbCommand cmd = db.GetSqlStringCommand(sb.ToString());
            db.AddInParameter(cmd, "@Name", DbType.String, name);
            return db.ExecuteNonQuery(cmd) > 0 ? true : false;
        }


        public Dictionary<string, WorkOrderItem> SelectAllMapper()
        {
            var list = new Dictionary<string, WorkOrderItem>();
            Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("UIDB");
            DataAccessor<WorkOrderItem> myAccessor;
            myAccessor = db.CreateSqlStringAccessor("select * from [workorder]",
                MapBuilder<WorkOrderItem>.MapAllProperties().
                //Map(p => p.Comments).WithFunc(dataRecord =>
                //    {
                //        int content = (int)dataRecord["Status"];
                //        if (content == 0)
                //        {
                //            return "Active";
                //        }
                //        else if (content == 1)
                //        {
                //            return "Waiting";
                //        }
                //        else
                //        {
                //            return "Finished";
                //        }
                //    }).
                Build()
                );
            list = myAccessor.Execute()
                .ToDictionary<WorkOrderItem, string>
                ((item) =>
                {
                    return item.Name;
                });
            return list;
        }

    }


    //public class WorkorderDAL
    //{
    //    //private DataSet myDataset;

    //    public WorkorderDAL()
    //    {
    //        FillDataTable();
    //        //Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("UIDB");
    //        //myDataset = new DataSet();
    //        //Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("MachineConfig");
    //        //string sql = "select * from [products$]";
    //        //DbCommand cmd = db.GetSqlStringCommand(sql);

    //        //string productsTable = "[products$]";

    //        //// Retrieve the initial data.
    //        //db.LoadDataSet(cmd, myDataset, productsTable);

    //        //// Get the table that will be modified.
    //        //DataTable dTable = myDataset.Tables[productsTable];



    //    }

    //    private DataSet dataSet = null;
    //    private DataTable dTable = null;

    //    public DataTable DTable
    //    {
    //        get { return dTable; }
    //        set { dTable = value; }
    //    }
    //    DbCommand insertCommand = null;
    //    DbCommand deleteCommand = null;
    //    DbCommand updateCommand = null;
    //    string tableName = "workorder";

    //    public void FillDataTable()
    //    {
    //        Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("UIDB");
    //        string sql = "SELECT [Name], [Status], [Privilege], [Quantity], [Finished], [Remaining], [Start" +
    //            "Time], [Endtime], [Pass], [Fail], [Comments] FROM [workorder]";
    //        //DbCommand dw = db.GetSqlStringCommand(sql);
    //        //DataTable  dt= db.ExecuteDataSet(dw).Tables[0];

    //        DataSet productsDataSet = new DataSet();
    //        DbCommand cmd = db.GetSqlStringCommand(sql);        

    //        //恢复原始数据
    //        db.LoadDataSet(cmd, productsDataSet, tableName);
    //        //获取数据表格
    //        dTable = productsDataSet.Tables[tableName];

    //        //提供插入,更新,删除存储过程
    //        insertCommand = db.GetSqlStringCommand(@"INSERT INTO [workorder] ([Name], [Status], [Privilege], [Quantity], [Finished], [Remaining], [StartTime], [Endtime], [Pass], [Fail], [Comments]) VALUES "+
    //            "(@Name, @Status, @Privilege, @Quantity, @Finished, @Remaining, @StartTime, @Endtime, @Pass, @Fail, @Comments)");   
    //        db.AddInParameter(insertCommand, "@Name", DbType.String, "Name", DataRowVersion.Current);
    //        db.AddInParameter(insertCommand, "@Status", DbType.Int32, "Status", DataRowVersion.Current);
    //        db.AddInParameter(insertCommand, "@Privilege", DbType.Int32, "Privilege", DataRowVersion.Current);
    //        db.AddInParameter(insertCommand, "@Quantity", DbType.Int32, "Quantity", DataRowVersion.Current);
    //        db.AddInParameter(insertCommand, "@Finished", DbType.Int32, "Finished", DataRowVersion.Current);
    //        db.AddInParameter(insertCommand, "@Remaining", DbType.Int32, "Remaining", DataRowVersion.Current);
    //        db.AddInParameter(insertCommand, "@StartTime", DbType.String, "StartTime", DataRowVersion.Current);
    //        db.AddInParameter(insertCommand, "@Endtime", DbType.String, "Endtime", DataRowVersion.Current);
    //        db.AddInParameter(insertCommand, "@Pass", DbType.Int32, "Pass", DataRowVersion.Current);
    //        db.AddInParameter(insertCommand, "@Fail", DbType.Int32, "Fail", DataRowVersion.Current);
    //        db.AddInParameter(insertCommand, "@Comments", DbType.String, "Comments", DataRowVersion.Current);

    //        deleteCommand = db.GetSqlStringCommand(@"delete from products where Name=@Name");
    //        db.AddInParameter(deleteCommand, "@Name", DbType.String, "Name", DataRowVersion.Current);

    //        //updateCommand = db.GetSqlStringCommand(sql);   
    //        //db.AddInParameter(updateCommand, "@CollegeID", DbType.Int32, "CollegeID", DataRowVersion.Current);
    //        //db.AddInParameter(updateCommand, "@Name", DbType.String, "Name", DataRowVersion.Current);
    //    }

    //    public void UpdateDataTable()
    //    {
    //        Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("UIDB");
    //        //通过DataSet更新数据库
    //        int rowsAffected = db.UpdateDataSet(dataSet, tableName, insertCommand, updateCommand, deleteCommand,
    //        Microsoft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Standard);
    //    }

    //}
}
